# _*_ coding:utf-8 _*_
# @Time  : 2022.10.17
# @Author: zizlee
from fastapi import APIRouter, Body, Query
from pydantic import BaseModel
from typing import List
from fastapi.encoders import jsonable_encoder
from db import FAConnection
from v1_all_api.all_utils import datetime_utils
from v1_all_api.all_response import AllResponse

stock_api = APIRouter()


class StockItem(BaseModel):
    stock_date: int
    rdb_id: int
    block_name: str
    variety_en: str
    variety_name: str
    stock_name: str
    stock_unit: str
    frequency: str
    date_latest: str
    stock_latest: float
    ring_rate: float
    same_rate1: float
    same_rate2: float
    highest1: float
    highest2: float
    highest3: float
    highest0: float
    lowest0: float
    c_level: float
    value_start: str
    is_fine: int


@stock_api.post('/')  # 保存库存数据
def save_datan_commodity_stock(stock_list: List[StockItem] = Body(...)):
    stock_list = jsonable_encoder(stock_list)
    # 先更新一遍再插入一遍
    up_sql = """
        UPDATE datan_commodity_stock SET block_name=%(block_name)s,
          variety_en=%(variety_en)s,variety_name=%(variety_name)s,stock_name=%(stock_name)s,stock_unit=%(stock_unit)s,
          frequency=%(frequency)s,date_latest=%(date_latest)s,stock_latest=%(stock_latest)s,
          ring_rate=%(ring_rate)s,same_rate1=%(same_rate1)s,
          highest1=%(highest1)s,highest2=%(highest2)s,highest3=%(highest3)s,highest0=%(highest0)s,
          lowest0=%(lowest0)s,c_level=%(c_level)s,value_start=%(value_start)s,is_fine=%(is_fine)s 
        WHERE stock_date=%(stock_date)s AND rdb_id=%(rdb_id)s;
    """
    in_sql = """
        INSERT IGNORE INTO datan_commodity_stock (stock_date,rdb_id,block_name,variety_en,variety_name,
          stock_name,stock_unit,frequency,date_latest,stock_latest,
          ring_rate,same_rate1,same_rate2,highest1,highest2,highest3,highest0,
          lowest0,c_level,value_start,is_fine) 
        VALUES (%(stock_date)s,%(rdb_id)s,%(block_name)s,%(variety_en)s,%(variety_name)s,
          %(stock_name)s,%(stock_unit)s,%(frequency)s,%(date_latest)s,%(stock_latest)s,
          %(ring_rate)s,%(same_rate1)s,%(same_rate2)s,%(highest1)s,%(highest2)s,%(highest3)s,%(highest0)s,
          %(lowest0)s,%(c_level)s,%(value_start)s,%(is_fine)s);
    """
    db_conn = FAConnection()
    up_count, _ = db_conn.execute(up_sql, param=stock_list, keep_conn=True, many=True)
    in_count, _ = db_conn.insert(in_sql, param=stock_list, many=True)
    message = '操作成功,更新数据{}条,新增数据{}条!'.format(up_count, in_count)
    return AllResponse.operate_successfully(msg=message)


@stock_api.get('/')  # 获取库存数据
def get_datan_commodity_stock(date: str = Query(...)):
    stock_date = datetime_utils.auth_datetime_string(date, f='%Y%m%d')
    if not stock_date:
        return AllResponse.validate_error(msg='query date is invalid!')
    sql = """
        SELECT * FROM datan_commodity_stock WHERE stock_date=%s ORDER BY variety_en;
    """
    db_conn = FAConnection()
    records = db_conn.query(sql, param=[stock_date])
    records.sort(key=lambda x: (x['block_name'], x['variety_en']))
    for row in records:
        row['value_start'] = row['value_start'][:7]
        for rate_key in ['ring_rate', 'same_rate1', 'same_rate2']:
            if row[rate_key] == 999999:
                row[rate_key] = ''
            if row[rate_key] and row[rate_key] > 999:
                row[rate_key] = round(row[rate_key], 0)
            row[rate_key] = '%.2f%%' % row[rate_key] if row[rate_key] else row[rate_key]
    return AllResponse.operate_successfully(data=records)
